package com.glsc.ngateway.common.base.repo.mysql.gateway.platform;


import com.glsc.ngateway.common.base.domain.mysql.gateway.platform.flow.FlowEventUnionImporove;
import com.glsc.ngateway.common.api.flowable.dto.FlowParamAuthDto;
import com.glsc.ngateway.common.api.flowable.dto.FlowParamSearchDto;
import com.glsc.ngateway.common.base.domain.mysql.gateway.system.VEhrAdminStaff;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface FlowEventUnionImporveRepo extends JpaRepository<FlowEventUnionImporove, String>, JpaSpecificationExecutor<FlowEventUnionImporove> {

    @Query(value = "select m from FlowEventUnionImporove m where 1=1 " +
            "and (:#{#paramSearchDto.startUser} is null OR m.flowCreateOperatorAccount = :#{#paramSearchDto.startUser})  " +
            "and (:#{#paramSearchDto.title} is null OR m.processTitle LIKE CONCAT('%',:#{#paramSearchDto.title},'%') )  " +
            "and (:#{#paramSearchDto.processDefineKey} is null OR m.processDefineKey = :#{#paramSearchDto.processDefineKey} )  " +
            "and (:#{#paramSearchDto.systemCode} is null OR m.systemCode = :#{#paramSearchDto.systemCode} )  " +
            "and (coalesce(:#{#paramSearchDto.systemCodes}, null) is null or m.systemCode in (:#{#paramSearchDto.systemCodes}))" +
            "and (COALESCE(:#{#paramSearchDto.eventCause}, null) IS NULL OR m.eventCause IN :#{#paramSearchDto.eventCause}) " +
            "and (:#{#paramSearchDto.beginDateLocalDate} is null OR m.processCreateTime >= :#{#paramSearchDto.beginDateLocalDate} )  " +
            "and (:#{#paramSearchDto.endDateLocalDate} is null OR m.processCreateTime <= :#{#paramSearchDto.endDateLocalDate} )  " +
            "and (:#{#paramSearchDto.happenBeginDate} is null OR m.eventHappenTime >= :#{#paramSearchDto.happenBeginDate} )  " +
            "and (:#{#paramSearchDto.happenEndDate} is null OR m.eventHappenTime <= :#{#paramSearchDto.happenEndDate} )  " +
            "and (:#{#paramSearchDto.mainClass} is null OR SUBSTRING(m.systemCode, 1, 2) = :#{#paramSearchDto.mainClass} )  " +
            "and (:#{#paramSearchDto.hasProcessEnd} is null OR m.isClose = :#{#paramSearchDto.hasProcessEnd} )  " +
            "and (coalesce(:#{#paramSearchDto.hasProcessEndList}, null) is null OR m.isClose in (:#{#paramSearchDto.hasProcessEndList} ))  " +
            "and (coalesce(:#{#paramSearchDto.eventGrade}, null) is null or m.eventGrade in (:#{#paramSearchDto.eventGrade}))" +
            "and (coalesce(:#{#paramSearchDto.adminList}, null) is null or m.systemAdminAccount in (:#{#paramSearchDto.adminList})) " +
            "and (coalesce(:#{#paramSearchDto.deptNames}, null) is null or m.systemBelongDepartment in (:#{#paramSearchDto.deptNames})) " +
            "and (coalesce(:#{#paramSearchDto.systemBelongDepartment}, null) is null or m.systemBelongDepartment in (:#{#paramSearchDto.systemBelongDepartment})) " +
            "order by m.processCreateTime desc")
    Page<FlowEventUnionImporove> searchByParam(Pageable pageable, FlowParamSearchDto paramSearchDto);

    @Query(value = "select m from FlowEventUnionImporove m  where 1=1 " +
            "and (:#{#paramSearchDto.startUser} is null OR m.flowCreateOperatorAccount = :#{#paramSearchDto.startUser})  " +
            "and (:#{#paramSearchDto.title} is null OR m.processTitle LIKE CONCAT('%',:#{#paramSearchDto.title},'%') )  " +
            "and (:#{#paramSearchDto.processDefineKey} is null OR m.processDefineKey = :#{#paramSearchDto.processDefineKey} )  " +
            "and (:#{#paramSearchDto.systemCode} is null OR m.systemCode = :#{#paramSearchDto.systemCode} )  " +
            "and (coalesce(:#{#paramSearchDto.systemCodes}, null) is null or m.systemCode in (:#{#paramSearchDto.systemCodes}))" +
            "and (COALESCE(:#{#paramSearchDto.eventCause}, null) IS NULL OR m.eventCause IN :#{#paramSearchDto.eventCause}) " +
            "and (:#{#paramSearchDto.beginDateLocalDate} is null OR m.processCreateTime >= :#{#paramSearchDto.beginDateLocalDate} )  " +
            "and (:#{#paramSearchDto.endDateLocalDate} is null OR m.processCreateTime <= :#{#paramSearchDto.endDateLocalDate} )  " +
            "and (:#{#paramSearchDto.happenBeginDate} is null OR m.eventHappenTime >= :#{#paramSearchDto.happenBeginDate} )  " +
            "and (:#{#paramSearchDto.happenEndDate} is null OR m.eventHappenTime <= :#{#paramSearchDto.happenEndDate} )  " +
            "and (:#{#paramSearchDto.mainClass} is null OR SUBSTRING(m.systemCode, 1, 2) = :#{#paramSearchDto.mainClass} )  " +
            "and (:#{#paramSearchDto.hasProcessEnd} is null OR m.isClose = :#{#paramSearchDto.hasProcessEnd} )  " +
            "and (coalesce(:#{#paramSearchDto.hasProcessEndList}, null) is null OR m.isClose in (:#{#paramSearchDto.hasProcessEndList} ))  " +
            "and (coalesce(:#{#paramSearchDto.eventGrade}, null) is null or m.eventGrade in (:#{#paramSearchDto.eventGrade}))" +
            "and (coalesce(:#{#paramSearchDto.adminList}, null) is null or m.systemAdminAccount in (:#{#paramSearchDto.adminList})) " +
            "and (coalesce(:#{#paramSearchDto.deptNames}, null) is null or m.systemBelongDepartment in (:#{#paramSearchDto.deptNames})) " +
            "and (coalesce(:#{#paramSearchDto.systemBelongDepartment}, null) is null or m.systemBelongDepartment in (:#{#paramSearchDto.systemBelongDepartment})) " +
            "and (m.systemBelongDepartment in (:#{#authCondition.belongDepartment})  or m.systemCode in (:#{#authCondition.adminSystem})" +
            " or m.processId in (:#{#authCondition.processIds}) or m.flowCreateOperatorAccount in (:#{#authCondition.creatorEmps}) or m.systemAdminAccount in (:#{#authCondition.systemAdminEmps})  " +
            ")" +
            "order by m.processCreateTime desc")
    Page<FlowEventUnionImporove> searchByParamWith(Pageable pageable, FlowParamSearchDto paramSearchDto, FlowParamAuthDto authCondition);

    @Query(value = "select m from FlowEventUnionImporove m where 1=1 " +
            "and (:#{#paramSearchDto.startUser} is null OR m.flowCreateOperatorAccount = :#{#paramSearchDto.startUser})  " +
            "and (:#{#paramSearchDto.title} is null OR m.processTitle LIKE CONCAT('%',:#{#paramSearchDto.title},'%') )  " +
            "and (:#{#paramSearchDto.processDefineKey} is null OR m.processDefineKey = :#{#paramSearchDto.processDefineKey} )  " +
            "and (:#{#paramSearchDto.systemCode} is null OR m.systemCode = :#{#paramSearchDto.systemCode} )  " +
            "and (coalesce(:#{#paramSearchDto.systemCodes}, null) is null or m.systemCode in (:#{#paramSearchDto.systemCodes}))" +
            "and (COALESCE(:#{#paramSearchDto.eventCause}, null) IS NULL OR m.eventCause IN :#{#paramSearchDto.eventCause}) " +
            "and (:#{#paramSearchDto.beginDateLocalDate} is null OR m.processCreateTime >= :#{#paramSearchDto.beginDateLocalDate} )  " +
            "and (:#{#paramSearchDto.endDateLocalDate} is null OR m.processCreateTime <= :#{#paramSearchDto.endDateLocalDate} )  " +
            "and (:#{#paramSearchDto.happenBeginDate} is null OR m.eventHappenTime >= :#{#paramSearchDto.happenBeginDate} )  " +
            "and (:#{#paramSearchDto.happenEndDate} is null OR m.eventHappenTime <= :#{#paramSearchDto.happenEndDate} )  " +
            "and (:#{#paramSearchDto.mainClass} is null OR SUBSTRING(m.systemCode, 1, 2) = :#{#paramSearchDto.mainClass} )  " +
            "and (:#{#paramSearchDto.hasProcessEnd} is null OR m.isClose = :#{#paramSearchDto.hasProcessEnd} )  " +
            "and (coalesce(:#{#paramSearchDto.hasProcessEndList}, null) is null OR m.isClose in (:#{#paramSearchDto.hasProcessEndList} ))  " +
            "and (coalesce(:#{#paramSearchDto.eventGrade}, null) is null or m.eventGrade in (:#{#paramSearchDto.eventGrade}))" +
            "and (coalesce(:#{#paramSearchDto.adminList}, null) is null or m.systemAdminAccount in (:#{#paramSearchDto.adminList})) " +
            "and (coalesce(:#{#paramSearchDto.deptNames}, null) is null or m.systemBelongDepartment in (:#{#paramSearchDto.deptNames})) " +
            "and (coalesce(:#{#paramSearchDto.systemBelongDepartment}, null) is null or m.systemBelongDepartment in (:#{#paramSearchDto.systemBelongDepartment})) " +
            "and (m.systemBelongDepartment in (:#{#authCondition.belongDepartment})  " +
            " or m.processId in (:#{#authCondition.processIds}) or m.flowCreateOperatorAccount in (:#{#authCondition.creatorEmps}) or m.systemAdminAccount in (:#{#authCondition.systemAdminEmps})  " +
            ")" +
            "order by m.processCreateTime desc")
    Page<FlowEventUnionImporove> searchByParamBelongDepartment(Pageable pageable, FlowParamSearchDto paramSearchDto, FlowParamAuthDto authCondition);

    @Query(value = "select m from FlowEventUnionImporove m  where 1=1 " +
            "and (:#{#paramSearchDto.startUser} is null OR m.flowCreateOperatorAccount = :#{#paramSearchDto.startUser})  " +
            "and (:#{#paramSearchDto.title} is null OR m.processTitle LIKE CONCAT('%',:#{#paramSearchDto.title},'%') )  " +
            "and (:#{#paramSearchDto.processDefineKey} is null OR m.processDefineKey = :#{#paramSearchDto.processDefineKey} )  " +
            "and (:#{#paramSearchDto.systemCode} is null OR m.systemCode = :#{#paramSearchDto.systemCode} )  " +
            "and (coalesce(:#{#paramSearchDto.systemCodes}, null) is null or m.systemCode in (:#{#paramSearchDto.systemCodes}))" +
            "and (COALESCE(:#{#paramSearchDto.eventCause}, null) IS NULL OR m.eventCause IN :#{#paramSearchDto.eventCause}) " +
            "and (:#{#paramSearchDto.beginDateLocalDate} is null OR m.processCreateTime >= :#{#paramSearchDto.beginDateLocalDate} )  " +
            "and (:#{#paramSearchDto.endDateLocalDate} is null OR m.processCreateTime <= :#{#paramSearchDto.endDateLocalDate} )  " +
            "and (:#{#paramSearchDto.happenBeginDate} is null OR m.eventHappenTime >= :#{#paramSearchDto.happenBeginDate} )  " +
            "and (:#{#paramSearchDto.happenEndDate} is null OR m.eventHappenTime <= :#{#paramSearchDto.happenEndDate} )  " +
            "and (:#{#paramSearchDto.mainClass} is null OR SUBSTRING(m.systemCode, 1, 2) = :#{#paramSearchDto.mainClass} )  " +
            "and (:#{#paramSearchDto.hasProcessEnd} is null OR m.isClose = :#{#paramSearchDto.hasProcessEnd} )  " +
            "and (coalesce(:#{#paramSearchDto.hasProcessEndList}, null) is null OR m.isClose in (:#{#paramSearchDto.hasProcessEndList} ))  " +
            "and (coalesce(:#{#paramSearchDto.eventGrade}, null) is null or m.eventGrade in (:#{#paramSearchDto.eventGrade}))" +
            "and (coalesce(:#{#paramSearchDto.adminList}, null) is null or m.systemAdminAccount in (:#{#paramSearchDto.adminList})) " +
            "and (coalesce(:#{#paramSearchDto.deptNames}, null) is null or m.systemBelongDepartment in (:#{#paramSearchDto.deptNames})) " +
            "and (coalesce(:#{#paramSearchDto.systemBelongDepartment}, null) is null or m.systemBelongDepartment in (:#{#paramSearchDto.systemBelongDepartment})) " +
            "and ( m.systemCode in (:#{#authCondition.adminSystem})" +
            " or m.processId in (:#{#authCondition.processIds}) or m.flowCreateOperatorAccount in (:#{#authCondition.creatorEmps}) or m.systemAdminAccount in (:#{#authCondition.systemAdminEmps})  " +
            ")" +
            "order by m.processCreateTime desc")
    Page<FlowEventUnionImporove> searchByParamAdminSystem(Pageable pageable, FlowParamSearchDto paramSearchDto, FlowParamAuthDto authCondition);

    @Query(value = "select m from FlowEventUnionImporove m  where 1=1 " +
            "and (:#{#paramSearchDto.startUser} is null OR m.flowCreateOperatorAccount = :#{#paramSearchDto.startUser})  " +
            "and (:#{#paramSearchDto.title} is null OR m.processTitle LIKE CONCAT('%',:#{#paramSearchDto.title},'%') )  " +
            "and (:#{#paramSearchDto.processDefineKey} is null OR m.processDefineKey = :#{#paramSearchDto.processDefineKey} )  " +
            "and (:#{#paramSearchDto.systemCode} is null OR m.systemCode = :#{#paramSearchDto.systemCode} )  " +
            "and (coalesce(:#{#paramSearchDto.systemCodes}, null) is null or m.systemCode in (:#{#paramSearchDto.systemCodes}))" +
            "and (COALESCE(:#{#paramSearchDto.eventCause}, null) IS NULL OR m.eventCause IN :#{#paramSearchDto.eventCause}) " +
            "and (:#{#paramSearchDto.beginDateLocalDate} is null OR m.processCreateTime >= :#{#paramSearchDto.beginDateLocalDate} )  " +
            "and (:#{#paramSearchDto.endDateLocalDate} is null OR m.processCreateTime <= :#{#paramSearchDto.endDateLocalDate} )  " +
            "and (:#{#paramSearchDto.happenBeginDate} is null OR m.eventHappenTime >= :#{#paramSearchDto.happenBeginDate} )  " +
            "and (:#{#paramSearchDto.happenEndDate} is null OR m.eventHappenTime <= :#{#paramSearchDto.happenEndDate} )  " +
            "and (:#{#paramSearchDto.mainClass} is null OR SUBSTRING(m.systemCode, 1, 2) = :#{#paramSearchDto.mainClass} )  " +
            "and (:#{#paramSearchDto.hasProcessEnd} is null OR m.isClose = :#{#paramSearchDto.hasProcessEnd} )  " +
            "and (coalesce(:#{#paramSearchDto.hasProcessEndList}, null) is null OR m.isClose in (:#{#paramSearchDto.hasProcessEndList} ))  " +
            "and (coalesce(:#{#paramSearchDto.eventGrade}, null) is null or m.eventGrade in (:#{#paramSearchDto.eventGrade}))" +
            "and (coalesce(:#{#paramSearchDto.adminList}, null) is null or m.systemAdminAccount in (:#{#paramSearchDto.adminList})) " +
            "and (coalesce(:#{#paramSearchDto.deptNames}, null) is null or m.systemBelongDepartment in (:#{#paramSearchDto.deptNames})) " +
            "and (coalesce(:#{#paramSearchDto.systemBelongDepartment}, null) is null or m.systemBelongDepartment in (:#{#paramSearchDto.systemBelongDepartment})) " +
            "and (" +
            "  m.processId in (:#{#authCondition.processIds}) or m.flowCreateOperatorAccount in (:#{#authCondition.creatorEmps}) or m.systemAdminAccount in (:#{#authCondition.systemAdminEmps})  " +
            ")" +
            "order by m.processCreateTime desc")
    Page<FlowEventUnionImporove> searchByParamCommon(Pageable pageable, FlowParamSearchDto paramSearchDto, FlowParamAuthDto authCondition);

    @Query(value = "select * from v_flow_event_union_improve m  where 1=1 " +
            "and (:#{#paramSearchDto.startUser} is null OR m.flow_create_operator_account = :#{#paramSearchDto.startUser})  " +
            "and (:#{#paramSearchDto.title} is null OR m.process_title LIKE CONCAT('%',:#{#paramSearchDto.title},'%') )  " +
            "and (:#{#paramSearchDto.processDefineKey} is null OR m.process_define_key = :#{#paramSearchDto.processDefineKey} )  " +
            "and (:#{#paramSearchDto.systemCode} is null OR m.system_code = :#{#paramSearchDto.systemCode} )  " +
            "and (coalesce(:#{#paramSearchDto.systemCodes}, null) is null or m.system_code in (:#{#paramSearchDto.systemCodes}))" +
            "and (COALESCE(:#{#paramSearchDto.eventCause}, null) IS NULL OR m.event_cause IN (:#{#paramSearchDto.eventCause})) " +
            "and (:#{#paramSearchDto.beginDateLocalDate} is null OR m.process_create_time >= :#{#paramSearchDto.beginDateLocalDate} )  " +
            "and (:#{#paramSearchDto.endDateLocalDate} is null OR m.process_create_time <= :#{#paramSearchDto.endDateLocalDate} )  " +
            "and (:#{#paramSearchDto.happenBeginDate} is null OR m.event_happen_time >= :#{#paramSearchDto.happenBeginDate} )  " +
            "and (:#{#paramSearchDto.happenEndDate} is null OR m.event_happen_time <= :#{#paramSearchDto.happenEndDate} )  " +
            "and (:#{#paramSearchDto.mainClass} is null OR SUBSTRING(m.system_code, 1, 2) = :#{#paramSearchDto.mainClass} )  " +
            "and (:#{#paramSearchDto.hasProcessEnd} is null OR m.is_close = :#{#paramSearchDto.hasProcessEnd} )  " +
            "and (coalesce(:#{#paramSearchDto.hasProcessEndList}, null) is null OR m.is_close in (:#{#paramSearchDto.hasProcessEndList} ))  " +
            "and (coalesce(:#{#paramSearchDto.eventGrade}, null) is null or m.event_grade in (:#{#paramSearchDto.eventGrade}))" +
            "and (coalesce(:#{#paramSearchDto.adminList}, null) is null or m.system_admin_account in (:#{#paramSearchDto.adminList})) " +
            "and (coalesce(:#{#paramSearchDto.deptNames}, null) is null or m.system_belong_department in (:#{#paramSearchDto.deptNames})) " +
            "and (coalesce(:#{#paramSearchDto.systemBelongDepartment}, null) is null or m.system_belong_department in (:#{#paramSearchDto.systemBelongDepartment})) " +
            " and (COALESCE(:#{#authCondition.admin}, null) IS null " +
            "or " +
            " ( m.process_id IN (:#{#authCondition.processIds}) " +
            "or m.system_code in (select a.system_class_no from t_gw_system_member  me inner join t_gw_system_main a on me.op_id = a.op_id where 1=1 " +
            "            and me.deleted = 0 and me.member_type = '1' and me.status = '1' " +
            "            and me.member_account in (:staffs) ) " +
            "or m.flow_create_operator_account in (:staffs) " +
            "or m.system_admin_account in (:staffs)  " +
            " ) " +
            " ) " +
            "order by m.process_create_time desc" ,nativeQuery = true)
    Page<FlowEventUnionImporove> searchByParamView(Pageable pageable, FlowParamSearchDto paramSearchDto, FlowParamAuthDto authCondition, List<VEhrAdminStaff> staffs);
}
